1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| select a.date_list,ifnull(b.count,0) as count
from (
select curdate() as date_list
union all
select date_sub(curdate(),interval 1 day) as date_list
union all
select date_sub(curdate(),interval 2 day) as date_list
union all
select date_sub(curdate(),interval 3 day) as date_list
union all
select date_sub(curdate(),interval 4 day) as date_list
union all
select date_sub(curdate(),interval 5 day) as date_list
union all
select date_sub(curdate(),interval 6 day) as date_list
) as a left join (
select date(gmt_create) as datetime,count(*) as count from alarm_message where student_status = "OUT_OF_SCHOOL"
group by date(gmt_create)
) as b on a.date_list = b.datetime
|